Backup Scheduling, Recovery Testing, and Disaster Recovery Planning in MySQL

Course: MySQL Training

Topic: Backup Scheduling, Recovery Testing, and Disaster Recovery Planning

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

Official Documentation: MySQL Documentation

1. Introduction to Backup Scheduling, Recovery Testing, and Disaster Recovery

Database backup and disaster recovery are essential responsibilities of a database administrator (DBA). MySQL databases often store important business information, including customer records, product details, orders, payments, employee information, and application data. Unexpected hardware failures, accidental deletions, software errors, cyberattacks, or natural disasters can make this information unavailable or cause permanent data loss.

Backup scheduling helps ensure that database copies are created regularly. Recovery testing verifies that these backups can actually restore the database when needed. Disaster recovery planning defines the procedures, responsibilities, infrastructure, and communication processes required to restore database services after a serious incident.

These three activities work together to protect data, reduce downtime, and support business continuity.

1.1 What Is Backup Scheduling?

Backup scheduling is the process of automatically running database backups at predefined times or intervals. Instead of relying on someone to create backups manually, an organization establishes a consistent schedule based on its data volume, business requirements, and acceptable data-loss risk.

For example, an online shopping application might create a full backup every night and retain MySQL binary logs so that database changes can be recovered to a specific point in time.

1.2 What Is Recovery Testing?

Recovery testing is the process of restoring a backup into a separate environment and checking whether the database and application work correctly. A backup file alone does not guarantee recoverability. It might be incomplete, corrupted, incompatible, or missing required transaction logs.

Recovery testing identifies these problems before a real emergency occurs.

1.3 What Is Disaster Recovery Planning?

Disaster recovery planning is the preparation of a documented strategy for restoring database systems and related services after a major disruption. It includes backup locations, recovery procedures, responsible personnel, infrastructure requirements, security controls, communication plans, and recovery targets.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain the purpose of MySQL database backups and recovery planning.
• Differentiate full, incremental, physical, and logical backups.
• Design a backup schedule based on business requirements.
• Use MySQL tools such as mysqldump and mysql to create and restore logical backups.
• Understand binary logs and point-in-time recovery.
• Automate backup jobs using operating-system scheduling tools.
• Test database restoration and validate recovered data.
• Define Recovery Point Objective (RPO) and Recovery Time Objective (RTO).
• Develop a disaster recovery plan for a MySQL application.
• Apply backup retention, access control, encryption, and monitoring practices.

3. Understanding Database Backup Fundamentals

3.1 What Is a Database Backup?

A database backup is a separate copy of database information and, where required, the supporting files or logs needed to restore it. Backups should be stored in a protected location that remains accessible even if the primary database server becomes unavailable.

Backups can protect against:

• Accidental deletion of tables, rows, or entire databases.
• Incorrect UPDATE or DELETE statements.
• Database corruption and storage failures.
• Application bugs that modify data incorrectly.
• Failed deployments or database migrations.
• Ransomware and other security incidents.
• Server failures, fire, flooding, and other site-level disasters.

3.2 Important Backup Terms

Important: Replication is not a substitute for backups. An accidental deletion or corrupted change can be replicated to another server.

4. Types of MySQL Backups

4.1 Full Backup

A full backup captures the database objects and data included in the backup operation. It provides a baseline for recovery and can simplify restoration because the administrator does not need to combine several incremental backups.

Example using mysqldump:

mysqldump -u backup_user -p --single-transaction --routines --events --triggers --databases company_db > company_db_full.sql

This command creates a logical SQL dump of the specified database, including routines, events, and triggers. The --single-transaction option provides a consistent snapshot for transactional tables such as InnoDB without holding a table lock for the entire dump. It does not guarantee a consistent backup of nontransactional tables if they are being modified.

4.2 Logical Backup

A logical backup stores database objects and data as SQL statements or another logical representation. It can be useful when moving data between servers or MySQL environments.

Common tool:

mysqldump -u backup_user -p --databases company_db > company_db.sql

Advantages:

• Human-readable SQL output in typical SQL dump files.
• Useful for migration and selective restoration.
• Can be restored through the MySQL command-line client.

Limitations:

• Large databases can take significant time to export and import.
• Restoration can be slower than restoring a suitable physical backup.
• Server-level configuration and every external dependency are not automatically captured.

4.3 Physical Backup

A physical backup copies database files or uses a database-aware physical backup utility. It can be more efficient for large databases but requires appropriate procedures to ensure the copied files form a consistent and restorable backup.

Physical backups may be created using MySQL Enterprise Backup or supported third-party tools. Do not assume that simply copying a running MySQL data directory produces a valid backup.

4.4 Incremental Backup

An incremental backup captures changes made since a previous backup. Incremental strategies can reduce storage requirements and backup duration, but restoring may require a base backup and a sequence of incremental backups.

Incremental backup capabilities depend on the selected MySQL edition and backup tool. A typical mysqldump workflow does not automatically provide a native incremental backup chain.

4.5 Binary Log Backup

MySQL binary logs record database changes relevant to replication and point-in-time recovery. When binary logging is enabled and the necessary logs are retained, an administrator can restore a suitable full backup and replay changes up to a chosen time or position.

Check the binary logging configuration:

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;

Availability and command support can vary by MySQL version. On versions that do not support SHOW BINARY LOG STATUS, use the version-appropriate status command documented for that release.

4.6 Comparison of Backup Types

5. Planning an Effective Backup Schedule

A backup schedule should reflect how frequently data changes, how much data the organization can afford to lose, how quickly services must return, and how long backups take to complete.

5.1 Factors to Consider

• Business criticality: A payment system may require more frequent protection than an internal reporting database.
• Transaction volume: Databases with constant updates may need continuous binary log protection.
• Database size: Large databases may require physical backup tools or a carefully planned backup window.
• Recovery objectives: The schedule must support the agreed RPO and RTO.
• Storage capacity: Estimate the space required for full backups, logs, and retained copies.
• Maintenance windows: Schedule resource-intensive jobs to minimize application impact.
• Regulatory requirements: Retention and access rules may be defined by contracts or regulations.
• Restore duration: A backup is useful only if it can be restored within the required timeframe.

5.2 Example Backup Schedule

This is an example rather than a universal standard. A high-transaction production system may need more frequent backups, more frequent binary log archiving, and more frequent recovery exercises.

5.3 Example Daily Backup Plan

1. At 12:30 AM, confirm backup storage capacity and database connectivity.
2. At 1:00 AM, start the scheduled full backup.
3. Record the start time, end time, exit status, and backup file size.
4. Validate that the backup file exists and is readable.
5. Copy the backup to a separate protected location.
6. Archive binary logs according to the recovery requirements.
7. Send a success or failure notification to the responsible team.
8. Delete expired backups only after verifying retention rules and confirming that required recovery chains remain intact.

6. Automating MySQL Backups with mysqldump

6.1 Create a Backup Directory

On a Linux server, create a dedicated directory for database backups:

sudo mkdir -p /var/backups/mysql
sudo chmod 700 /var/backups/mysql

Set the directory owner and permissions according to the account that runs the backup job. The backup account should have only the permissions needed for the backup operation.

6.2 Create a Backup Script

The following example demonstrates a basic Linux backup script. In a production environment, use a protected MySQL option file or an approved secrets manager instead of placing passwords in scripts or command-line arguments.

#!/bin/bash
set -Eeuo pipefail
umask 077

DB_NAME="company_db"
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP="$(date +%Y-%m-%d_%H-%M-%S)"
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql"
TEMP_FILE="${BACKUP_FILE}.tmp"
LOG_FILE="${BACKUP_DIR}/backup.log"

mkdir -p "$BACKUP_DIR"

{
  echo "Backup started: $(date)"

  mysqldump \
    --defaults-extra-file=/etc/mysql/backup-client.cnf \
    --single-transaction \
    --routines \
    --events \
    --triggers \
    --databases "$DB_NAME" > "$TEMP_FILE"

  test -s "$TEMP_FILE"
  mv "$TEMP_FILE" "$BACKUP_FILE"

  echo "Backup completed: $BACKUP_FILE"
  echo "Backup finished: $(date)"
} >> "$LOG_FILE" 2>&1

Save the script as /usr/local/bin/mysql_backup.sh and make it executable:

sudo chmod 700 /usr/local/bin/mysql_backup.sh

The MySQL client option file must be securely created and readable only by the account that runs the job. For example, its contents could be:

[client]
user=backup_user
password=REPLACE_WITH_A_SECURE_SECRET
host=localhost

Protect the option file using restrictive file permissions, such as chmod 600, and use your organization's approved secret-management process. Do not commit credentials to source control.

The script writes to a temporary file and renames it only after the dump command succeeds. This helps avoid treating an incomplete dump as a finished backup. It does not by itself verify that the backup can be restored or that an off-site copy has been created.

6.3 Schedule the Backup Using Cron

On Linux, cron can run the backup script automatically. Open the crontab for the account that should execute the backup:

crontab -e

Add the following example to run the script daily at 1:00 AM:

0 1 * * * /usr/local/bin/mysql_backup.sh

Cron uses the server's configured time zone. Confirm the system time, script permissions, environment variables, and logging configuration before relying on the schedule.

6.4 Scheduling with Windows Task Scheduler

On Windows, create a scheduled task that runs a PowerShell script or batch file containing the approved backup command. Configure the task to:

1. Run under a dedicated service account.
2. Use the correct MySQL client path.
3. Use a protected option file for authentication.
4. Write logs and return an appropriate exit status.
5. Retry or alert on failure.
6. Run under the correct time zone and service-account permissions.

7. Backup Retention and Storage Management

Backup retention determines how long backup files and recovery logs are preserved. A good retention policy balances recovery needs, legal requirements, storage costs, and security risks.

7.1 Example Retention Policy

These periods are examples only. Choose actual retention periods based on business requirements and applicable policies.

7.2 Backup Storage Best Practices

• Keep backups separate from the primary database storage.
• Maintain a protected copy in another failure domain or geographic location.
• Use encryption at rest and secure transfer methods.
• Restrict who can read, delete, or modify backup files.
• Monitor available storage and backup growth.
• Use immutable storage or deletion protection where appropriate.
• Keep backup manifests and metadata, including timestamps and relevant binary log coordinates.
• Test access to remote backup locations during recovery exercises.

8. Understanding Recovery Point Objective (RPO) and Recovery Time Objective (RTO)

8.1 Recovery Point Objective (RPO)

RPO is the maximum amount of data loss, measured in time, that a business is prepared to tolerate after an incident.

Example: If a business sets an RPO of 15 minutes, its recovery process should aim to lose no more than 15 minutes of committed transactions. Meeting this target depends on backup frequency, binary log protection, storage reliability, and the ability to recover those logs.

8.2 Recovery Time Objective (RTO)

RTO is the target time within which a service should be restored after an outage.

Example: If the RTO is two hours, the team should plan and test a recovery process that returns the required database service within that time.

8.3 RPO and RTO Comparison

RPO and RTO are business targets, not automatic guarantees. They must be demonstrated through testing.

9. Understanding Point-in-Time Recovery in MySQL

Point-in-time recovery (PITR) restores a database to a specific moment using a suitable full backup and the binary logs containing subsequent database changes. It is useful when an accidental operation damages data but the administrator wants to recover transactions that occurred before that operation.

9.1 How Point-in-Time Recovery Works

1. Create or identify a valid full backup.
2. Confirm that the binary logs covering the recovery period are available.
3. Restore the full backup into an isolated recovery environment.
4. Identify the time or binary log position immediately before the unwanted operation.
5. Replay the required binary log events up to the selected recovery point.
6. Validate the restored data and application behavior.
7. Make the recovered service available only after the recovery decision and checks are complete.

9.2 Enable Binary Logging

Binary logging is configured in the MySQL server configuration. A simplified example is:

[mysqld]
server_id=101
log_bin=mysql-bin
binlog_expire_logs_seconds=604800

This example configures a binary log and a seven-day automatic expiration interval. Select values that match the required recovery window and storage capacity. In a production environment, ensure that required logs have been securely archived before they expire. Automatic expiration on the source server must not remove the only copy of logs needed for recovery.

Configuration changes may require a MySQL server restart. Review the documentation for the installed MySQL version before changing production settings.

9.3 Inspect Binary Log Files

SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

These commands help inspect binary logging status and retention configuration.

9.4 Restore a Logical Backup

For a logical dump that includes database creation statements, an example restoration command is:

mysql --defaults-extra-file=/etc/mysql/restore-client.cnf < company_db_full.sql

Run restoration commands in an isolated test or recovery environment first. Check the dump contents, target server, database names, and permissions before restoring, especially when a dump contains statements that create, replace, or modify databases.

9.5 Replay Binary Logs to a Specific Time

After restoring the full backup, an administrator can use mysqlbinlog to replay the required binary log events. A simplified example is:

mysqlbinlog \
  --start-datetime="2026-10-16 00:00:00" \
  --stop-datetime="2026-10-16 01:25:00" \
  /secure/binlogs/mysql-bin.000123 \
  /secure/binlogs/mysql-bin.000124 \
  | mysql --defaults-extra-file=/etc/mysql/restore-client.cnf

This example is illustrative. The binary log files must be in the correct sequence and cover the required interval. The stop time must be chosen carefully, and timestamp boundaries may not be sufficient when transactions share close timestamps. For high-risk recovery, inspect the logs and consider precise event positions or transaction boundaries. Test the complete process before using it in production.

Important: Restoring a backup and replaying binary logs can change many records. Always verify the target server and database state before running the replay command.

10. Recovery Testing: Why It Is Essential

Recovery testing proves that the backup and recovery procedures work under controlled conditions. A successful backup job only confirms that a backup process reported success; it does not prove that the data can be restored correctly.

10.1 Objectives of Recovery Testing

• Confirm that backup files are complete and accessible.
• Verify that database objects and data can be restored.
• Measure actual recovery duration against the RTO.
• Measure recoverable data loss against the RPO.
• Identify missing credentials, configuration files, plugins, or dependencies.
• Validate database consistency and application functionality.
• Train team members to perform recovery under pressure.

10.2 Types of Recovery Tests

11. Step-by-Step Recovery Testing Procedure

Step 1: Select the Backup

Choose a recent backup and confirm its timestamp, source database, backup type, and associated binary logs. Verify that the selected backup meets the intended recovery scenario.

Step 2: Prepare an Isolated Test Environment

Use a separate MySQL instance, virtual machine, or approved test environment. Avoid restoring test backups over the production database. Ensure the test environment has adequate CPU, memory, storage, and compatible MySQL versions.

Step 3: Verify the Backup File

Check the file's existence, size, checksum, and backup-job logs.

ls -lh /var/backups/mysql/
sha256sum /var/backups/mysql/company_db_full.sql

Save the checksum in a trusted backup manifest or separate protected system. A checksum can help detect file changes, but it does not prove that a SQL dump is logically correct or restorable.

Step 4: Restore the Backup

Create the required test environment and restore the backup using the appropriate procedure. For a logical dump, the command might be:

mysql --defaults-extra-file=/etc/mysql/restore-client.cnf < /var/backups/mysql/company_db_full.sql

Use credentials with the required privileges and verify the destination server before starting.

Step 5: Validate Database Objects

Check that the expected database, tables, views, stored procedures, functions, and events exist.

SHOW DATABASES;
USE company_db;
SHOW TABLES;
SHOW CREATE TABLE customers;
SHOW TRIGGERS;

Check routines and scheduled events as required. The exact validation steps depend on which objects were included in the backup and the privileges of the test account.

Step 6: Validate Data

Compare key table counts, important business records, and expected totals against trusted reference data.

SELECT COUNT(*) AS customer_count FROM customers;
SELECT COUNT(*) AS order_count FROM orders;
SELECT SUM(total_amount) AS order_total FROM orders;

Example queries assume that the specified tables and columns exist. In a real test, define expected results in advance and account for the backup's timestamp so that differences caused by later transactions are interpreted correctly.

Step 7: Test Application Connectivity

• Confirm that the application can connect to the recovered database.
• Verify that expected users and permissions are available.
• Test important read operations and, in a controlled test environment, safe write operations.
• Check stored procedures, triggers, views, and scheduled events where relevant.
• Review database and application error logs.

Step 8: Measure Recovery Time

Record the time from the start of the recovery procedure until the database and required application functions are usable. Compare this duration with the agreed RTO.

Step 9: Record the Test Results

Document the backup identifier, test date, MySQL version, restoration duration, validation results, failures, corrective actions, and the person who approved the test.

12. Backup Verification and Integrity Checks

12.1 File-Level Checks

File-level checks help detect missing, empty, or unexpectedly modified backup files.

test -s /var/backups/mysql/company_db_full.sql
sha256sum /var/backups/mysql/company_db_full.sql
gzip -t /var/backups/mysql/company_db_full.sql.gz

The compression check applies only to a gzip-compressed file. The filename in the example is illustrative and should match the actual file.

12.2 SQL Dump Checks

For logical dumps, review the backup command's exit status and logs. When appropriate, inspect the beginning and end of the file and perform a test restore. Text searches alone cannot establish that a dump is complete or valid.

12.3 Database-Level Checks

After restoration, compare table counts, business totals, schema definitions, and application behavior. For InnoDB databases, the consistency and availability of the recovered data should be validated using the chosen recovery procedure and application-specific checks.

12.4 Automated Alerts

Monitoring should report:

• Backup job failures.
• Unexpectedly small or large backup files.
• Missed backup schedules.
• Insufficient storage capacity.
• Missing binary log files.
• Failed remote uploads.
• Backups that have not been tested within the required period.

13. Disaster Recovery Planning for MySQL

A disaster recovery plan (DRP) is a documented set of actions for restoring database services following a major disruption. It should be understandable by the people who will execute it, including staff who did not originally configure the system.

13.1 Identify Critical Systems

List the databases and applications that must be restored. Record their business owners, technical owners, dependencies, priority, and required recovery targets.

Example:

These are illustrative targets, not recommended universal values. The business owner must approve realistic targets based on impact analysis, technical feasibility, and budget.

13.2 Identify Possible Disaster Scenarios

• Primary database server hardware failure.
• Storage device failure or filesystem corruption.
• Accidental deletion or incorrect data modification.
• Database software upgrade failure.
• Credential compromise or ransomware.
• Cloud-region or data-center outage.
• Loss of the primary network or storage system.
• Failure of a scheduled backup process.

13.3 Define Recovery Strategies

Different incidents may require different recovery methods.

13.4 Establish Recovery Roles

A disaster recovery plan should assign clear responsibilities.

• Incident coordinator: Declares or coordinates the recovery process.
• Database administrator: Restores MySQL data and validates database consistency.
• Infrastructure team: Prepares servers, storage, networking, and access.
• Application team: Tests application functionality and dependencies.
• Security team: Investigates security incidents and approves safe restoration.
• Business owner: Confirms business readiness and prioritizes service restoration.
• Communications lead: Provides status updates to stakeholders.

14. Designing a Disaster Recovery Runbook

A recovery runbook contains the exact procedures required during a disaster. It should be stored somewhere accessible even if the primary database environment is unavailable.

14.1 Recommended Runbook Contents

1. System name, database names, and business owner.
2. Incident severity and criteria for declaring a disaster.
3. RPO, RTO, and service restoration priorities.
4. Backup locations and instructions for accessing them securely.
5. MySQL versions, configuration requirements, and software dependencies.
6. Credentials retrieval procedure and encryption-key access process.
7. Restore commands and point-in-time recovery instructions.
8. Binary log inventory and retention information.
9. Application connection and validation steps.
10. Network, DNS, firewall, and access-control changes.
11. Escalation contacts and communication procedures.
12. Approval steps before returning the service to production.
13. Post-incident review and documentation requirements.

14.2 Example Recovery Sequence

1. Declare the incident and notify the recovery team.
2. Identify the failure scope and protect unaffected systems.
3. Confirm the latest valid backup and required binary logs.
4. Prepare replacement or isolated recovery infrastructure.
5. Restore the selected backup.
6. Replay required binary logs to the approved recovery point.
7. Validate schema, data, permissions, and application behavior.
8. Obtain the required business and technical approvals.
9. Redirect application traffic to the recovered service.
10. Monitor errors, performance, and data consistency.
11. Document the recovery timeline and outstanding issues.

The actual sequence should be adjusted for the incident. For example, during a suspected security breach, containment and investigation must happen before recovered systems are connected to potentially compromised infrastructure.

15. High Availability, Replication, and Disaster Recovery

High availability and disaster recovery are related but different concepts. High availability focuses on reducing service interruption, while disaster recovery focuses on restoring service after a serious failure.

A robust strategy often combines backups, binary log archiving, replication or high availability where appropriate, isolated storage, and tested recovery procedures.

16. Security Best Practices for Backups and Recovery

16.1 Restrict Backup Access

Backup files may contain the entire database, including sensitive personal and business information. Apply least-privilege access controls to backup creation, retrieval, restoration, and deletion.

16.2 Encrypt Backups

Use approved encryption for backup files at rest and secure encrypted channels when transferring them. Protect encryption keys separately from the backup files so that a single compromised storage account cannot expose both the data and its keys.

16.3 Protect Credentials

• Do not place database passwords directly in scripts or source control.
• Use protected client option files or an approved secrets manager.
• Restrict permissions on credentials and backup configuration files.
• Use separate accounts for backup and restore activities where practical.
• Review and rotate credentials according to organizational policy.

16.4 Protect Against Ransomware

• Maintain isolated or immutable backup copies.
• Separate backup administration from primary database administration where feasible.
• Limit deletion rights and monitor suspicious access.
• Test restoration from protected copies.
• Investigate affected systems before restoring them to production.

16.5 Maintain Audit Records

Record who created, downloaded, restored, or deleted backups. Audit logs help investigate incidents and verify that recovery procedures followed approved controls.

17. Monitoring Backup Jobs and Recovery Readiness

Backup monitoring should verify more than whether a scheduled task ran. It should confirm that the output is usable, stored in the expected location, copied to protected storage, and covered by the retention policy.

17.1 Key Monitoring Metrics

17.2 Backup Failure Response

When a backup job fails:

1. Read the complete job log and identify the failure reason.
2. Check database connectivity and account permissions.
3. Check free storage space and destination accessibility.
4. Confirm that the backup tool is compatible with the MySQL version.
5. Correct the underlying issue and rerun the backup safely.
6. Verify the resulting backup and update the monitoring status.
7. Escalate if the failure creates a risk of missing the recovery objectives.

18. Common Backup and Recovery Problems

19. Practical Project: MySQL Backup and Disaster Recovery Lab

Project Objective

Build a small backup and recovery workflow for a sample MySQL database. The project should demonstrate automated backup creation, backup verification, test restoration, recovery documentation, and disaster recovery planning.

19.1 Create a Sample Database

CREATE DATABASE dr_lab;
USE dr_lab;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_name, email)
VALUES
('Aarav Shah', '[email protected]'),
('Meera Patel', '[email protected]');

INSERT INTO orders (customer_id, total_amount, order_status)
VALUES
(1, 2500.00, 'Completed'),
(2, 1800.00, 'Pending');

19.2 Create a Logical Backup

mysqldump -u backup_user -p --single-transaction --routines --events --triggers --databases dr_lab > dr_lab_backup.sql

Store the file in a protected location and record its timestamp and checksum.

19.3 Create a Test Database

CREATE DATABASE dr_lab_test;

For this exercise, the dump includes the original database name. To restore into a different database, either create a dump without database-creation statements and select the target database during import, or carefully adapt the SQL dump for the test environment. Do not blindly import a dump that targets the production database.

One straightforward test approach is to restore the dump into an isolated MySQL instance where the original database name does not conflict with other data:

mysql --defaults-extra-file=/etc/mysql/restore-client.cnf < dr_lab_backup.sql

19.4 Verify the Recovered Data

USE dr_lab;
SELECT COUNT(*) AS customers_count FROM customers;
SELECT COUNT(*) AS orders_count FROM orders;
SELECT SUM(total_amount) AS total_order_value FROM orders;

Compare the results with the expected values recorded when the backup was created. Repeat the same validation in the test environment.

19.5 Document the Recovery Procedure

Create a recovery document that specifies:

• Backup file location and checksum.
• Database and MySQL version.
• Required credentials and access procedure.
• Restore commands and target environment.
• Expected row counts and business totals.
• Estimated restore duration and measured test duration.
• Recovery roles, escalation contacts, and approval steps.

19.6 Project Deliverables

• A working scheduled backup script.
• A protected backup file and verification record.
• A successful test restoration.
• A backup retention plan.
• A recovery test report.
• A disaster recovery runbook with proposed RPO and RTO targets.

20. Best Practices for Backup Scheduling and Disaster Recovery

• Automate backups instead of relying entirely on manual tasks.
• Choose backup frequency according to the approved RPO.
• Use binary logs when point-in-time recovery is required.
• Store copies outside the primary database failure domain.
• Encrypt backups and restrict access to sensitive data.
• Monitor job results and alert on failures.
• Verify backups after creation and test restoration regularly.
• Measure actual recovery time instead of assuming the RTO will be met.
• Document database versions, dependencies, configuration, and access procedures.
• Review backup schedules whenever data volume or business requirements change.
• Keep required backup chains and binary logs for the entire recovery window.
• Ensure recovery instructions are accessible if the main environment is unavailable.
• Perform post-incident reviews and update the runbook after every important test.

21. Exercises and Practice Questions

Exercise 1: Backup Scheduling

Design a backup schedule for an online store that receives orders throughout the day. Specify the full backup frequency, binary log archival approach, retention period, and alerting rules.

Exercise 2: Recovery Testing

Create a logical backup of a sample database, restore it to a separate MySQL instance, and compare table counts and important business totals.

Exercise 3: Point-in-Time Recovery

Describe how you would recover a database to a point immediately before an accidental deletion. Identify the full backup and binary logs required.

Exercise 4: RPO and RTO

A business can tolerate no more than 10 minutes of data loss and requires the database to be restored within 90 minutes. Define the RPO and RTO, then explain how you would test whether the system meets both targets.

Exercise 5: Disaster Recovery Plan

Write a disaster recovery runbook for a database server that becomes unavailable because of a storage failure. Include the recovery roles, backup location, restoration steps, validation checks, and approval process.

22. Interview Questions and Answers

Q1. What is backup scheduling in MySQL?

Answer: Backup scheduling is the automation of database backup jobs at predefined times or intervals to ensure that recoverable copies are created consistently.

Q2. Why is recovery testing necessary?

Answer: It verifies that backups can be restored, data is usable, application dependencies work, and recovery objectives can be met.

Q3. What is a disaster recovery plan?

Answer: It is a documented strategy for restoring database services after a major incident. It includes recovery procedures, responsibilities, infrastructure, security, communication, and validation.

Q4. What is the difference between RPO and RTO?

Answer: RPO defines the maximum tolerable data loss measured in time, while RTO defines the target time for restoring a service.

Q5. What is the purpose of MySQL binary logs?

Answer: Binary logs record database changes and support replication and point-in-time recovery when the required logs and a suitable backup are available.

Q6. What is point-in-time recovery?

Answer: It is the process of restoring a suitable backup and replaying binary log events to recover the database to a chosen time or event position.

Q7. Does replication replace backups?

Answer: No. Replication can reproduce accidental deletions or corrupted changes. Independent backups preserve historical recovery options.

Q8. How can MySQL backups be automated on Linux?

Answer: A shell script can run a backup utility such as mysqldump, while cron or a system scheduler executes the script at the required time.

Q9. How do you verify a backup?

Answer: Check the job exit status, logs, file existence, size, and checksum, then perform a test restoration and validate the recovered data.

Q10. What is a backup retention policy?

Answer: It defines how long backup copies and associated recovery logs are stored, based on business, technical, security, and compliance requirements.

Q11. Why should backups be stored off-site?

Answer: A separate location protects backup copies from incidents that affect the primary server, storage system, or physical site.

Q12. What should a recovery runbook contain?

Answer: It should contain the recovery sequence, backup locations, configuration requirements, secure access instructions, restore commands, validation checks, contacts, and approval steps.

Q13. What happens if required binary logs are missing?

Answer: Point-in-time recovery may be limited to the latest point supported by the available backup and complete log sequence. Missing logs can prevent recovery to the desired time.

Q14. How frequently should recovery tests be performed?

Answer: The frequency depends on system criticality, change rate, business requirements, and risk. High-criticality systems generally need more frequent and comprehensive testing.

Q15. How do you know whether an RTO is realistic?

Answer: Measure the complete restoration process in representative recovery exercises, including infrastructure preparation, database restoration, log replay, validation, and service cutover.

23. Quick Revision Checklist

• Understand full, logical, physical, and incremental backups.
• Know how to create a logical backup with mysqldump.
• Understand automated scheduling with cron or Windows Task Scheduler.
• Define a backup retention policy and protect backup storage.
• Understand RPO, RTO, and point-in-time recovery.
• Know how to restore a backup into an isolated environment.
• Validate schema, data, permissions, and application functionality.
• Monitor backup jobs, binary log coverage, and remote copies.
• Maintain a documented disaster recovery runbook.
• Test recovery procedures and record measured results.
• Use encryption, least privilege, and isolated or immutable backup copies.

24. Conclusion

Backup scheduling, recovery testing, and disaster recovery planning are essential parts of reliable MySQL database administration. A scheduled backup creates a recovery option, but only verification and restoration testing demonstrate that the backup is usable. Binary logs can extend recovery capabilities by supporting point-in-time recovery, while clear disaster recovery procedures help teams restore services in a controlled and timely manner.

A strong strategy combines automated backups, protected off-site copies, suitable retention, secure credentials, monitoring, regular recovery exercises, and documented RPO and RTO targets. By practicing these techniques, database administrators can reduce the impact of data loss, shorten service interruptions, and improve the resilience of business applications.

Continue Learning: Explore the MySQL Training Course to strengthen your database administration skills, or register for a course demo to learn more about the training.